const illness = require('../db/illness')
const { QueryTypes } = require('sequelize');

exports.deleteIllness = async function (illness_id) {
    let result = await illness.sequelize.query(`delete  from  illness where illness_id = '${illness_id}' ;`, { type: QueryTypes.SELECT });
    return  result;
}

exports.updateIllness = async function (illness_id,illname,illclass,symptom,cause,spread,hostplant,characteristic,feature,habit,method,passing) {
    let result = await illness.sequelize.query(`update illness set illname='${illname}',class='${illclass}',symptom='${symptom}',cause='${cause}',spread='${spread}',hostplant='${hostplant}',characteristic='${characteristic}',feature='${feature}',habit='${habit}',method='${method}',passing='${passing}' where illness_id = '${illness_id}' ;`);

    return  result;
}

exports.insertIllness = async function (illname,illclass,symptom,cause,spread,hostplant,characteristic,feature,habit,method,passing) {
    let result = await illness.sequelize.query(`insert into illness(illname,class,symptom,cause,spread,hostplant,characteristic,feature,habit,method,passing) values ('${illname}','${illclass}','${symptom}','${cause}','${spread}','${hostplant}','${characteristic}','${feature}','${habit}','${method}','${passing}') ;`, { type: QueryTypes.SELECT });

    return  result;
}

exports.getAllIllness = async function (pageNo) {
    let illnesses = await illness.sequelize.query(`select * from illness limit  ${(pageNo-1)*10}, 10;`,{ type: QueryTypes.SELECT });
    let counts = await illness.sequelize.query("select count(*) as counts from illness ;", { type: QueryTypes.SELECT });

        return {
            illnesses,
            counts
        } ;
}

exports.getIllness = async function (key) {
    let results= await illness.sequelize.query(`select * from illness  where illname like "%${key}%" or symptom LIKE "%${key}%" or hostplant LIKE "%${key}%" or characteristic LIKE "%${key}%" or feature LIKE "%${key}%"`,
    { type: QueryTypes.SELECT });
    while (!results[0]) {
        key = key.slice(1,key.length);
        if (key ==='') {
            return false;
        }
        results= await illness.sequelize.query(`select * from illness  where illname like "%${key}%" or symptom LIKE "%${key}%" or hostplant LIKE "%${key}%" or characteristic LIKE "%${key}%" or feature LIKE "%${key}%"`,
    { type: QueryTypes.SELECT });
    }
    return results;
}

exports.getIllnessById = async function (key) {
    let results= await illness.sequelize.query(`select * from illness  where illness_id = "${key}"`,
    { type: QueryTypes.SELECT });
    
    return results;
}

exports.selectIllnessByName = async function (illname) {
    let results= await illness.sequelize.query(`select * from illness  where illname = "${illname}"`,
    { type: QueryTypes.SELECT });
    
    return results;
}